﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
namespace QuanLySieuThi.Help
{
    public class CDataProvider
    {
        //Khai báo biến
        private string _strConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=QLBHST.mdb";
        private OleDbConnection _connection;
        private string _error = "";
        //Khai báo property
        public string Error
        {
            get { return _error; }
            private set { _error = value; }
        }
        //Tạo đối tượng kết nối
        public CDataProvider()
        {
            _connection = new OleDbConnection(_strConnectionString);
        }
        //Mở kết nối đến CSDL
        public bool OpenConnect()
        {
            try
            {
                if (_connection == null)
                    _connection = new OleDbConnection(_strConnectionString);
                if (_connection.State == ConnectionState.Open)
                    _connection.Close();
                _connection.Open();
                return true;
            }
            catch (SqlException ex)
            {
                Error = ex.Message;
            }
            return false;
        }
        //Đóng kết nối
        public bool CloseConnect()
        {
            try
            {
                if (_connection != null)
                    if (_connection.State == ConnectionState.Open)
                        _connection.Close();
                return true;
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                return false;
            }
        }
        //Thực thi truy vấn với các lệnh insert, update, delete
        public bool SqlExcuteNonQuery(string procName, params OleDbParameter[]
        sqlParameters)
        {
            try
            {
                if (OpenConnect())
                {
                    OleDbCommand sqlCommand = _connection.CreateCommand();
                    //sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = procName;
                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);
                    sqlCommand.ExecuteNonQuery();

                    Error = "";
                    return true;
                    
                    
                }
                return false;
            }
            catch (SqlException ex)
            {
                _error = ex.Message;
                return false;
            }
            finally
            {
                CloseConnect();
            }
        }
        public bool KiemTra(string tenDN, string matKhau)
        {
            try
            {
                if (OpenConnect())
                {
                    OleDbCommand sqlCommand = _connection.CreateCommand();
                    //sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "Select * from TaiKhoan where TenDN = " + "'tenDN'" + " and MatKhau = " + "'matKhau.ToString()'";
                    sqlCommand.ExecuteNonQuery();
                    Error = "";
                    return true;
                }
                return false;
            }
            catch (SqlException ex)
            {
                _error = ex.Message;
                return false;
            }
            finally
            {
                CloseConnect();
            }
        }
        //Thực thi truy vấn với lệnh Select
        public DataTable SqlExcuteQuery(string procName, params OleDbParameter[]
        sqlParameters)
        {
            DataTable dt = null;
            try
            {
                if (OpenConnect())
                {
                    OleDbCommand sqlCommand = _connection.CreateCommand();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = procName;
                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);
                    var dbadapter = new OleDbDataAdapter(sqlCommand);
                    dt = new DataTable();
                    dbadapter.Fill(dt);
                    Error = "";
                }
            }
            catch (SqlException ex)
            {
                Error = ex.Message;
            }
            finally
            {
                CloseConnect();
            }
            return dt;
        }
        public DataTable LoadDS(string sql)
        {
            DataTable dt = null;
            try
            {
                if (OpenConnect())
                {
                    OleDbCommand cmd = _connection.CreateCommand();
                    cmd.CommandText = sql;
                    OleDbDataAdapter da = new OleDbDataAdapter  (cmd);
                    dt = new DataTable();
                    da.Fill(dt);
                    Error = "";
                }
            }
            catch (SqlException ex) { Error = ex.Message; }
            finally { CloseConnect(); }
            return dt;

        }
        //Trả về 1 giá trị đầu tiên
        public object SqlExcuteScalar(string procName, params OleDbParameter[]
        sqlParameters)
        {
            object value = null;
            try
            {
                if (OpenConnect())
                {
                    var sqlCommand = _connection.CreateCommand();
                    //sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = procName;
                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);
                    value = sqlCommand.ExecuteReader();
                    Error = "";
                }
            }
            catch (SqlException ex)
            {
                Error = ex.Message;
            }
            finally
            {
                CloseConnect();
            }
            return value;
        }
        
    }
}
